package oocl.com.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import oocl.com.dao.CustomerDao;
import oocl.com.pojo.Customer;
import oocl.com.util.DBUtil;

public class CopyOfCustomerDaoImpl implements CustomerDao {

	@Override
	//改了当前的所有表结构
	public int addCustomer(Customer c) {
		String sql = "insert into CustomerTable(id, name, sal, sex, birth) values (seq.nextval,?,?,?,?)";
		String sql1 = "insert into mapping(id,cid) values (?,?)";
		PreparedStatement pst =null;
		Connection con = null;
		int m = 0; 
		
		try {
//			con = DBUtil.createConnection();
			con =DBUtil.createConnectionByJNDI();
			pst = con.prepareStatement(sql);
			pst.setString(1, c.getName());
			pst.setDouble(2, c.getSal());
			pst.setBoolean(3, c.isSex());
			pst.setDate(4, new java.sql.Date(c.getBirth().getTime()));
			String buf[] = c.getFavs();
			String str = "";
			for(String i:buf){
				str += i + ":";
			}
//			pst.setString(5,str);
			
			m = pst.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			DBUtil.close(con, pst, null);
		}
		return m;
	}

	@Override
	public List<Customer> findAllCustomer() {
		List<Customer> list = new ArrayList<Customer>();
		String sql = "select * from CustomerTable";
		PreparedStatement pst = null;
		ResultSet rs = null;
//		Connection con = DBUtil.createConnection();
		Connection con =DBUtil.createConnectionByJNDI();
		
		try {
			pst = con.prepareStatement(sql);
			rs = pst.executeQuery();
			
			while(rs.next()){
				Customer c = new Customer();
				c.setId(rs.getInt("id"));
				c.setName(rs.getString("name"));
				c.setSal(rs.getDouble("sal"));
				c.setSex(rs.getBoolean("sex"));
				c.setBirth(new Date(rs.getDate("birth").getTime()));
				String buf[] = rs.getString("favs").split(":");
				c.setFavs(buf);
				
				list.add(c);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			DBUtil.close(con, pst, rs);
		}
		return list;
	}

	@Override
	public int deleteCustomer(int id) {
		String sql = "delete CustomerTable where id=?";//先创建一个模板
		PreparedStatement pst = null;
		Connection con = null;
		int m = 0;
		
		try {
			con = DBUtil.createConnection();
			pst = con.prepareStatement(sql);
			pst.setInt(1, id);
			m = pst.executeUpdate();
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
		finally{
			DBUtil.close(con, pst, null);
		}
		return m;
	}

	@Override
	public int updateCustomer(Customer c) {
		String sql = "update CustomerTable set name=?, sal=?, sex=?, birth=?, favs=? where id=?";//先创建一个模板
		PreparedStatement pst = null;
		Connection con = null;
		int m = 0;
		
		try {
			con = DBUtil.createConnection();
			pst = con.prepareStatement(sql);
			
			pst.setString(1, c.getName());
			pst.setDouble(2, c.getSal());
			pst.setBoolean(3, c.isSex());
			pst.setDate(4, new java.sql.Date(c.getBirth().getTime()));
			String buf[] = c.getFavs();
			String str = "";
			for(String i:buf){
				str += i + ":";
			}
			pst.setString(5,str);
			pst.setInt(6, c.getId());
			
			m = pst.executeUpdate();
		} catch (SQLException e1) {
			System.out.println("0000000000000000000000000");
			e1.printStackTrace();
		}
		finally{
			DBUtil.close(con, pst, null);
		}
		return m;
	}

	@Override
	public Customer loadCustomer(int id) {
		String sql = "select * from CustomerTable where id=?";
		PreparedStatement pst = null;
		ResultSet rs = null;
//		Connection con = DBUtil.createConnection();
		Connection con =DBUtil.createConnectionByJNDI();
		Customer c = null;
		try {
			pst = con.prepareStatement(sql);
			pst.setInt(1, id);
			rs = pst.executeQuery();
			
			if(rs.next()){
				c = new Customer();
				c.setId(rs.getInt("id"));
				c.setName(rs.getString("name"));
				c.setSal(rs.getDouble("sal"));
				c.setSex(rs.getBoolean("sex"));
				c.setBirth(new Date(rs.getDate("birth").getTime()));
				String buf[] = rs.getString("favs").split(":");
				c.setFavs(buf);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			DBUtil.close(con, pst, rs);
		}
		return c;
	}

}
